AWR Top 10 Foreground Events

In an ideal database CPU and I/O should be the top wait events.

If there are events like TX row lock contention and latch free then that means there is contention in your database.

The db file sequential read (which means index reads) should be higher as compared to db file scattered read (which means full table scans).

The following are 10 of the most common causes for wait events, along with explanations and potential solutions:

Cell Smart Table Scan The cell smart table scan event is what Oracle uses to account for time spent waiting for Full Table Scans that are offloaded. It is the most important new event on the Exadata platform. Its presence or absence can be used to verify whether a statement benefitted from Offloading or not.

Offloading only occurs when Oracle is able to do direct path reads. Consequently, this event replaces the direct path read event in most cases on Exadata. As with normal direct path reads, data is returned directly to the PGA of the requesing process on the database server. Blocks are not returned to the buffer cache

Exadata Smart Scan is a feature of the Database Machine. It offloads the data search and retrieval processing to the storage cell. Exadata Cell evaluate query predicates at the storage level to optimize the performance of certain classes of bulk data processing. For example the performance of queries that require a full table or index scans to evaluate selective predicates can be improved by pushing the database expression evaluations to the storage cell.

0. Cell Single Block Physical Read. This event is equivalent to the db file sequential read event used on non-Exadata platforms. Single block reads are used most often for index access paths (both the index block reads and the table block reads via rowids from the index lookups). They can also be used for a wide variety of other operations where it makes sense to read a single block.

If these are the only I/O wait events you see (and not together with multiblock reads) then it appears you are not using a full segment scan at all. Of course, sometimes single block reads show up due to other operations in the execution plan (like some index range scan) or due to chained rows in data blocks.

Exadata provides a large amount of flash cache (384G) on each storage cell. For that reason, physical reads (both multi-block and single-block) are considerably faster than on most disk-based storage systems.

1. DB File Scattered Read. This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes.

2. DB File Sequential Read. This event generally indicates a single block read (an index read, for example). A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is normal for this number to be large for a high-transaction, well-tuned system, but it can indicate problems in some circumstances. You should correlate this wait statistic with other known issues within the Statspack report, such as inefficient SQL.

These circumstances are usually interrelated. When they occur in conjunction with the appearance of the db file scattered read and db file sequential read in the Top 5 Wait Events section.


3. Free Buffer. This indicates your system is waiting for a buffer in memory, because none is currently available.

Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, if all your SQL is tuned.

Free buffer waits could also indicate that unselective SQL is causing data to flood the buffer cache with index blocks, leaving none for this particular statement that is waiting for the system to process. This normally indicates that there is a substantial amount of DML (insert/update/delete) being done and that the Database Writer (DBWR) is not writing quickly enough; the buffer cache could be full of multiple versions of the same buffer, causing great inefficiency.

To address this, you may want to consider accelerating incremental checkpointing, using more DBWR processes, or increasing the number of physical disks.

To investigate if this is an I/O problem, look at the File I/O Statistics.

4. Buffer Busy. This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache.

Buffer busy waits should not be greater than 1%.

Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out the type of wait:

  1. Move data to another block to avoid this hot block
  2. Increase the freelists on the table
  3. Use Locally Managed Tablespaces (LMTs).
  • Index block ==> Rebuild the index, partition the index, or use a reverse key index.

    • To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as "hot."

    When a DML occurs, Oracle Database writes information into the block, including all users who are "interested" in the state of the block (Interested Transaction List, ITL).

    To decrease waits in this area:

    1. Increase the INITRANS, which will create the space in the block to allow multiple ITL slots.

    2. Increase the PCTFREE on the table where this block exists (this writes the ITL information up to the number specified by maxtrans, when there are not enough slots built with the initrans that is specified).

    Brief Definition:

    Buffer busy wait happens when a session wants to access a database block in the buffer cache, but it cannot as the buffer is "busy". This indicates that there are some buffers in the buffer cache that multiple processes are attempting to either access concurrently while its being read from disk or waiting for another session's block change to complete.

    The two main cases where this can occur are the following:

    1. Another session is reading the block into the buffer
    2. Another session holds the buffer in an incompatible mode to our request

    Problem Confirmation:

    AWR/statspack report top timed event shows significant percentage of database time spent on this wait event.

    Reducing Waits:

    1. Identify the segments where buffer busy waits contention occurring using the reference notes.
    2. Identify the block type from v$waitstat or from statspack/awr(section: Buffer Wait Statistics).

    Freelist blocks/segment header:

    Concurrent INSERTs with a suboptimal freelist configuration can lead to buffer busy wait contention as multiple sessions attempt to insert data into the same block (because it appears on the freelist to them). Heavy INSERT activity by concurrent sessions can cause multiple sessions to attempt their insert into the same blocks because automatic segment space management (ASSM) is NOT used, AND there is only a single freelist, too few process freelists, and/or no freelist groups.

    The best solution is to use ASSM since it is sometimes tricky to arrive at a correct freelist or freelist group setting. Adding process freelists will help remove contention as each process will map to separate blocks. Freelists can be added at any time without rebuilding the table. Adding freelist groups will also remove contention by mapping processes to other freelists. This is of greatest benefit in RAC environments where the freelist group block itself will be associated with an instance, but will still help in single instance environments as well. The table must be rebuilt to change the freelist group setting.

    Data blocks:

    Concurrent INSERTs or updates may see contention when a related index has a key that is constantly increasing (e.g., a key based on a sequence number). Index leaf blocks may see contention due to key values that are increasing steadily (using a sequence) and concentrated in a leaf block on the "right-hand side" of the index. Look at using reverse key indexes (if range scans aren't commonly used against the segment). A reverse key index will spread keys around evenly and avoid creating these hot leaf blocks. However, the reverse key index will not be usable for index range scans, so care must be taken to ensure that access is normally done via equality predicates. Eliminate HOT blocks access from the application. Many concurrent physical reads against the same blocks will result in buffer busy waits as one session gets to do the actual physical read, and the others will be blocked by the buffer busy wait event until the read completes. This is usually an indication that the SQL statement must be tuned. Oracle's SQL Tuning Advisor can help tune specific SQL statements quickly and easily if you are licensed to use the Enterprise Manager Tuning Pack.

    Undo header:

    Use automatic undo management or add more rollback segments. The waits can be amplified greatly when physical reads are slow due to poor I/O subsystem performance.

    Measuring Success:

    Once you have applied the changes to resolve the issues you have found, compare the latest AWR to the AWR that led you here via Guided Resolution (that AWR becomes your baseline). Look at the percentage decrease total wait time for this event. If there are still issues, re- evaluate those and address them according to the specific symptom.

    NOTE

    Automatic segment space management (ASSM) is a simpler and more efficient way of managing space within a segment. It eliminates any need to specify and tune the pctused, freelists, and freelist groups storage parameters for schema objects created in the tablespace.

    If any of these attributes are specified, they are ignored.

    Note:

    Note:

    Block change tracking buffer space

    When the session is waiting for the block change tracking buffer space event, it indicates that there is space waiting in the CTRW dba buffer.

    Several causes for this wait event are:

    1. Poor I/O performance on the disk where the change-tracking file resides.
    2. CTWR dba buffer is too small to record the number of concurrent block changes.

    Workings of CTWR:

    1. When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time they generate redo.
    2. When a commit is issued, the BCT information is copied to a shared area in Large Pool called 'CTWR dba buffer'.
    3. At the checkpoint, CTWR writes the information from the buffer to the change-tracking file.
    4. If contention for space in the CTWR dba buffer occurs, a wait event called, 'Block Change Tracking Buffer Space' is recorded.

    Library Cache Lock

    The library cache load lock Oracle metric occurs when the process is waiting for the opportunity to load an object or a piece of an object into the library cache. The loads of SQL statements are serialized and locked in exclusive mode, such that only one process can load an object or a piece of an object at a time. In general, all library cache waits are associated with non-reentrant SQL or an undersized shared pool.

    In general the library cache load lock occurs during periods of high activity within the library cache, especially with databases that do not use host variables and have a hard parsing due to non-reusable SQL.

    5. Latch Free. Latches are low-level queuing mechanisms (they're accurately referred to as mutual exclusion mechanisms) used to protect shared memory structures in the system global area (SGA).

    Latches are like locks on memory that are very quickly obtained and released. They are used to prevent concurrent access to a shared memory structure. If the latch is not available, a latch free miss is recorded.

    Most latch problems are related to:

    There are also latch waits related to bugs; check MetaLink for bug reports if you suspect this is the case.

    When latch miss ratios are greater than 0.5%, you should investigate the issue.

    If latch free waits are in the Top 5 Wait Events or high in the complete Wait Events list, look at the latch-specific sections of the AWR report to see which latches are contended for.

    6. Enqueue. An enqueue is a lock that protects a shared resource.

    Locks protect shared resources, such as data in a record, to prevent two people from updating the same data at the same time.

    An enqueue includes a queuing mechanism, which is FIFO (first in, first out).

    Note: Oracle's latching mechanism is not FIFO. Enqueue waits usually point to the following enqueues:

    How To Analyze the Wait Statistic: 'enq: HW - contention'
    Given the following situation. This information can be retrieved from a statspack, ADDM, ASH report or v$session_wait query:
    Event % Wait Time P1 Parameter P1 Value P2 Parameter P2 Value Parameter 3 P3 Value
    enq: HW - contention 62,81 name|mode 1213661190 table space 4 # block 17005691

    1. Determine the correct file and block number:

    select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(17005691) FILE#,
    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(17005691) BLOCK#
    from dual;

    FILE# BLOCK#
    ---------- ----------
    4 228475

    For bigfile tablespaces, do not use DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE, or you will get wrong results.
    Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_util.htm#i1002531
    In such a case, just use the tablespace# and assume p3 is the block number (there is no relative file number).
    2. Determine the object to which this block belongs to


    select owner, segment_type, segment_name
    from dba_extents
    where file_id = 4
    and 228475 between block_id and block_id + blocks - 1;

    OWNER SEGMENT_TYPE SEGMENT_NAME
    --------------- --------------- ------------------------------
    SCOTT LOBSEGMENT EMP_DATA_LOB

    Additionally, if the lock contention is currrently observed, we can find out the underlying segment using the following query:


    select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
    DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
    from v$lock
    where type = 'HW';

    As the 'enq HW - contention' may be caused by a number of different reasons, there are also several possible different solutions to alleviate or reduce contention.


    Things to check are:-

    1. Ensure that your lob segment is not frequently extending.
    2. Check I/O performance.
    3. A table containing a very busy lob segment may need partitioning in a manner that will evenly distribute concurrent DML across multiple partitions.
    4. Frequent lob space/chunk reclaimation can also cause 'enq HW - contention'

    In the case of point 4. there are a couple of options that may be able to be employed to provide either temporary relief or a workaround for the problem